﻿using FAMailUtils.Data.DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FAMailUtils.Data.DAO
{
    public class ClientDAO
    {
        DbHelper db = new DbHelper();
        public ClientDTO GetByClientID(int ClientID)
        {
            string sql = "select * from tblClient where ClientID = " + ClientID;
            ClientDTO result = new ClientDTO();
            result = (ClientDTO)db.GetObject(result, sql);
            return result;
        }

        public bool Update(ClientDTO dto)
        {
            string sql = @"update tblClient set CompanyName = @CompanyName, 
                        CompanyEmail = @CompanyEmail, 
                        CompanyFax = @CompanyFax, 
                        CompanyPhone = @CompanyPhone,
                        CompanyAddress = @CompanyAddress, CompanyLogo = @CompanyLogo from tblClient where ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "CompanyName", dto.CompanyName, 
                "CompanyEmail", dto.CompanyEmail, 
                "CompanyFax", dto.CompanyFax, 
                "CompanyPhone", dto.CompanyPhone, 
                "CompanyAddress", dto.CompanyAddress,
                "CompanyLogo", dto.CompanyLogo,
                "ClientID", dto.ClientID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public bool UpdateDomain(ClientDTO dto)
        {
            string sql = @"update tblClient set CompanyDomain = @CompanyDomain where ClientID = @ClientID";
            object obj = db.ExecuteScalar(sql, new object[] { "CompanyDomain", dto.CompanyDomain, "ClientID", dto.ClientID });
            bool result = false;
            bool.TryParse(obj + "", out result);
            return result;
        }

        public bool AddRoute(FAMailUtils.MailGun.CreateResult rout, int userid)
        {
            FAMailUtils.MailGun.RoutItem routItem = rout.route;
            string sql = "insert into tblmb_routes(id, CreateDate, domain, description, priority, expression, UserID, create_at) values(@id, getdate(), @domain, @description, @priority, @expression, @UserID, @create_at)";
            int i = db.ExecuteNoneQuery(sql, new object[] { "id", routItem.id, "domain", rout.domain, "description", routItem.description, "priority", routItem.priority, "expression", routItem.expression, "userid", userid, "create_at", routItem.created_at });
            return i > 0;
        }

        public bool UpdateRoute(FAMailUtils.MailGun.CreateResult rout, int userid)
        {
            FAMailUtils.MailGun.RoutItem routItem = rout.route;
            string sql = "update tblmb_routes set id=@id, CreateDate=getdate() where domain=@domain and expression=@expression";
            int i = db.ExecuteNoneQuery(sql, new object[] { "id", routItem.id, "domain", rout.domain, "expression", routItem.expression });
            return i > 0;
        }

        public string GetRouteIdByDomain(string domain)
        {
            string sql = "Select id from tblmb_routes where domain=@domain and isdeleted=0";
            return db.ExecuteScalar(sql, new object[] { "domain", domain })+"";
        }

        
        public bool CheckExistsDomain(string domain, int clientid)
        {
            string sql = "select * From tblclient where clientid<>@clientid and companydomain=@companydomain";
            DataTable table = db.GetTable(sql, new object[]{"clientid",clientid,"companydomain",domain});
            return table.Rows.Count == 0;
        }


        public void SaveRoute(MailGun.RoutItem route)
        {
            string sql = "delete from tblMB_Routes where expression='" + route.expression.Replace("'","''") + "' and id<>'" + route.id+"'";
            db.ExecuteNoneQuery(sql);
        }
    }
}
